
/* Prabhanjan, Aug 16, 2004
 * This procedure is used to get datat from BackLog_History_Table when AsOfDate is passed to sp_customer_report.
 */

alter      proc sp_backlog_reports_as_of_date
 
 -- Input parameter is process StepID;
 @Product_ID 		varchar(15),
 @Sales_Order_Number 	varchar(15),
 @Customer_ID 		varchar(15),
 @Sales_Person 		varchar(15),
 @asOfDate 		datetime,
 @Backlog_Type 		varchar(10),
 @Category		varchar(100),
 @Order_Type 		int

 as
 -- End_User field added into results table by gandhi on 13th Sep 2004
 declare @date_in datetime, @date_out datetime
 declare @results Table(Customer_Name varchar(50),Customer_Number varchar(50), Sales_Order_Number varchar(50),Customer_PO varchar(50), 
 SO_Date datetime, Sales_Person varchar(50), End_User varchar(50), Sales_Rep varchar(50), Sales_Hold varchar(10), Marketing_Hold varchar(10),Manufacturing_Hold varchar(10), 
 Finance_Hold varchar(10), Req_Date datetime, Schd_Date datetime, Rev_Date datetime, Part_Number varchar(50), Req_Qyt int, Rem_Qty int ,
 Unit_Price float, Status varchar(20), LineItem_No int,RevLevel int, BacklogType varchar(20), BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,ActYear int,FinYear int,Is_Expedite int)
 
 declare @booking_date  datetime -- Added by Basha (11/21/2003)
 declare @so_BK_Mt_I int, @so_Bk_Yr_I int, @so_Bk_Qt_I int, @so_Bk_Qt_End_I int -- Added by Basha (11/21/2003)
 declare @act_year int , @fin_year int -- Added By Basha (11/23/2003)

 declare @start_date datetime
 declare @end_date datetime
 declare @temp_varchar varchar(20)
 declare @tempDate datetime


 if @Backlog_Type = 'all'
 begin
 	set @backlog_Type = '0'
 end

 set @temp_varchar = convert(varchar, @asOfdate, 101)+' 12:00AM'
 set @start_date = convert(datetime, @temp_varchar)
 set @temp_varchar = null
 set @temp_varchar = convert(varchar, @asOfdate, 101)+' 11:59PM'
 set @end_date = convert(datetime, @temp_varchar)

 declare @lineItemCursor CURSOR
 
 if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	BHT.SalesOrder_No = @sales_order_number and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	BHT.Customer_ID = @customer_id and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and BHT.SalesOrder_No = @sales_order_number and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type

 end
 
 else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and BHT.Customer_ID = @customer_id and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	BHT.SalesOrder_No = @sales_order_number and BHT.Customer_ID = @customer_id and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	BHT.SalesOrder_No = @sales_order_number and Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type

 end 
 else  if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	BHT.SalesOrder_No = @sales_order_number and BHT.Customer_ID = @customer_id and Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	BHT.Customer_ID = @customer_id and Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and BHT.SalesOrder_No = @sales_order_number and BHT.Customer_ID = @customer_id and Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and BHT.Customer_ID = @customer_id and Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end
 
 else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID = '0' and @Sales_Person <> '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and BHT.SalesOrder_No = @sales_order_number and Sales_Person = @sales_person and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type

 end 
 
 else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person = '0')
 begin
	set @lineItemCursor = CURSOR for select LineItem_ID, Customer_Name, Customer_Number, BHT.SalesOrder_No, Customer_PO_Number,
		Booking_Date, Sales_Person, End_User, BHT.Regional_Rep, Req_Dock_Date, Schedule_Date, Revised_Date, Part_Number, BK_Requested_Qty, BK_Unit_Cost, BK_LineItem_Status, BK_Is_Expedite, isNUll(BK_Shipped_Qty, 0), BK_Is_Cancel, LineItem_No, Sales_Hold, Marketing_Hold, Operations_Hold, Finance_Hold, Revison_Level, BHT.SalesOrder_ID, BK_Backlog_Type
	from Backlog_History_Table BHT, Sales_Order SO where BHT.SalesOrder_ID = SO.SalesOrder_ID and
	Product_ID = @product_id and BHT.SalesOrder_No = @sales_order_number and BHT.Customer_ID = @customer_id and BHT.TIMESTAMP between @start_date and @end_date and SO.SO_Is_Sample = @Order_Type
 end

 declare @customer_Name varchar (50), @customer_number varchar (50), @so_no varchar (50)
 declare @line_item_id int, @customer_po varchar (50), @so_date datetime, @s_person varchar(50)
 declare @End_User varchar(50), @sales_rep varchar(50), @req_date datetime, @part_number varchar(50), @quantity int
 declare @unit_cost float, @sch_date datetime, @rev_date datetime,@status varchar (20), @line_item_no int
 declare @COOL_Is_Cancel int, @sales_hold varchar(50), @mktg_hold varchar(50), @mfg_hold varchar(50)
 declare @fin_hold varchar(50), @rev_level int, @rev_so_id int
 
 declare @is_expedite int
 declare @is_cancel int 
 declare @shipped_qty int

 declare @bk_backlog_type varchar(10)

 declare @quarter_cal_date datetime -- Added by Basha (11/23/2004
 
 open @lineItemCursor 
 fetch next from @lineItemCursor into @line_item_id,@customer_Name,@customer_number,@so_no,@customer_po,@so_date, 
 		@s_person, @End_User, @sales_rep,  @req_date,@sch_date,@rev_date,@part_number, @quantity, @unit_cost, @status, @is_expedite, @shipped_qty, @is_cancel, @line_item_no, @sales_hold, @mktg_hold, @mfg_hold, @fin_hold, @rev_level, @rev_so_id, @bk_backlog_type
 
 	while @@fetch_status = 0
 	begin
 		
 		-- Begin : Added by Basha (11/21/2003)
 		if (@Category = 'reqDockDate')
 		begin
 			set @quarter_cal_date = @req_date
 		end
 		else if (@Category = 'ScheduleDate')
 		begin
 			set @quarter_cal_date = @sch_date
 		end
 		else if (@Category = 'revDate')
 		begin
 			if ( @rev_date is not null)	
 			begin
 				set @quarter_cal_date = @rev_date
 			end
 			else
 			begin
 				set @quarter_cal_date = @sch_date
 				set @rev_date = @sch_date
 			end
 		end
 		else if (@Category = 'salesOrder' or @Category = 'partNumber')
 		begin
 			set @quarter_cal_date = @so_date
 		end
 
 
 		if( @quarter_cal_date is not null)
 		begin
 			set @so_Bk_Yr_I = DATEPART(yyyy, @quarter_cal_date)
 			set @act_year = @so_Bk_Yr_I
 			set @so_BK_Mt_I = DATEPART(m, @quarter_cal_date)
 			-- Modified by Prabhanjan (14 Apr 2004)
 			--if (@so_BK_Mt_I >=2 and @so_BK_Mt_I <=4)
 			if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
 			begin
 				set @so_Bk_Qt_I = 1
 				--set @so_Bk_Qt_End_I =4
 				set @so_Bk_Qt_End_I =3
 
 				set @fin_year = @so_Bk_Yr_I
 			end
 			--else if (@so_BK_Mt_I >=5 and @so_BK_Mt_I <=7)
 			else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)	
			begin
 				set @so_Bk_Qt_I = 2
 				--set @so_Bk_Qt_End_I =7
 				set @so_Bk_Qt_End_I =6
 
 				set @fin_year = @so_Bk_Yr_I
 			end
 			--else if (@so_BK_Mt_I >=8 and @so_BK_Mt_I <=10)
 			else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
 			begin
 				set @so_Bk_Qt_I = 3
 				--set @so_Bk_Qt_End_I =10
 				set @so_Bk_Qt_End_I =9
 
 				set @fin_year = @so_Bk_Yr_I
 			end						
 			--else if ((@so_BK_Mt_I =1) or (@so_BK_Mt_I >=11 and @so_BK_Mt_I <=12))
 			else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
 			begin
 				set @so_Bk_Qt_I = 4
 				--set @so_Bk_Qt_End_I =1
 				set @so_Bk_Qt_End_I =12
 
 				/*
 				if (@so_BK_Mt_I = 1) -- Added by Basha (11/23/2003)
 					set @fin_year = @so_Bk_Yr_I - 1
 
 				if @so_Bk_Mt_I = 12 or @so_Bk_Mt_I = 11 
 				begin
 					set @fin_year = @so_Bk_Yr_I
 					set @so_Bk_Yr_I = @so_Bk_Yr_I+1
 				end
 				*/
 			
 			end
 
 		end
 		else
 		begin
 			set @act_year = null
 			set @fin_year = null
 
 			set @so_BK_Mt_I = 0
 			set @so_Bk_Yr_I = null
 			set @so_Bk_Qt_I = 0
 			set @so_Bk_Qt_End_I = 0
 		end
 		-- End : Added by Basha (11/21/2003)		
 
 		select @rev_level = max(ChangeNo) from change_sales_order where salesorder_id = @rev_so_id and change_sales_order.CSO_Is_Sample = 1
		
			 
 		if @rev_level is null
		begin
			set @rev_level = 0
		end
 
		declare @temp_status varchar (20) -- , @temp_ps varchar (20)
 
 		set @temp_status = null -- Added by Basha (11/13/2003)
 
		if @is_cancel = 0
		begin
			set @status = 'CANCELLED'
		end  
		declare @rem_qty int
		set @rem_qty = @quantity - @shipped_qty 

		insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, End_User, Sales_Rep, 
			Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, 
			LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd , ActYear,FinYear, Is_Expedite)
		values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@End_User,@sales_rep,@sales_hold, 
			@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number, @quantity, @rem_qty, @unit_cost, 
			@status,@line_item_no,@rev_level, @bk_backlog_type, @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year, @is_expedite) 
 
		set @line_item_id = null
		set @temp_status = null
		set @rev_level = null
		set @rev_so_id = null
		set @is_cancel=null
		set @rev_date = null
 
 		set @so_date = null -- Added by Basha (11/21/2003)
 
 		fetch next from @lineItemCursor into  @line_item_id,@customer_Name,@customer_number,@so_no,@customer_po,@so_date, 
 			@s_person, @End_User, @sales_rep,  @req_date,@sch_date,@rev_date, @part_number, @quantity, @unit_cost, @status, @is_expedite, @shipped_qty, @is_cancel, @line_item_no, @sales_hold, @mktg_hold, @mfg_hold, @fin_hold, @rev_level, @rev_so_id, @bk_backlog_type
 	end
 	
 	-- End : @@fetch_Status = 0 for lineItemCursor
 
 	close @lineItemCursor
 	deallocate @lineItemCursor
 
 	
 	if @Backlog_Type <> '0' 
 	begin
 		
 		--select * from @results where BacklogType = @Backlog_Type
 		--select * from @results where BacklogType = @Backlog_Type order by So_Date , BookingYear , BookingQuarter 
 		if (@Category = 'reqDockDate')		
		begin
 			select * from @results where BacklogType = @Backlog_Type order by Req_Date
 		end
 		else if (@Category = 'ScheduleDate')
 		begin
 			select * from @results where BacklogType = @Backlog_Type order by Schd_Date
 		end
 		else if (@Category = 'revDate')
 		begin
 			select * from @results where BacklogType = @Backlog_Type order by Rev_Date
 		end
 		else if (@Category = 'salesOrder')
 		begin
 			select * from @results where BacklogType = @Backlog_Type order by Sales_Order_Number
 		end
 		else if (@Category = 'partNumber')
 		begin
 			select * from @results where BacklogType = @Backlog_Type order by Part_Number
 		end
 	end
 	else
 	begin
 		--select * from @results 
 		--select * from @results order by So_Date , BookingYear , BookingQuarter
 		if (@Category = 'reqDockDate')
 		begin
 			select * from @results order by Req_Date
 		end
 		else if (@Category = 'ScheduleDate')
 		begin
 			select * from @results order by Schd_Date
 		end
 		else if (@Category = 'revDate')
 		begin
 			select * from @results order by Rev_Date
 		end
 		else if (@Category = 'salesOrder')
 		begin
 			select * from @results order by Sales_Order_Number
 		end
 		else if (@Category = 'partNumber')
 		begin
 			select * from @results order by Part_Number
 		end
 	end
